package com.itrip.log.test;

import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.regex.Pattern;

import net.sf.jsqlparser.JSQLParserException;

import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlInsertStatement;
import com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser;
import com.alibaba.druid.sql.parser.SQLStatementParser;

/**
 * Function:XXX TODO add desc
 *
 * @date:2016年10月22日/下午7:42:23
 * @Author:coder_czp@126.com
 * @version:1.0
 */
public class SQLParsetest {

	/** 获取table的正则 */
	private static final Pattern INSERT_SQL_REGX = Pattern
			.compile("insert\\s+into\\s+(\\w+)", Pattern.CASE_INSENSITIVE);
	private static final Pattern UPDATE_SQL_REGX = Pattern.compile("update\\s+(\\w+)", Pattern.CASE_INSENSITIVE);
	private static final Pattern DEL_SQL_REGX = Pattern.compile("\\s+from\\s+(\\w+)", Pattern.CASE_INSENSITIVE);
	private static final Pattern SELECE_SQL_REGX = Pattern.compile("(\\S+)", Pattern.CASE_INSENSITIVE);

	public static void main(String[] args) throws JSQLParserException {
		String sql = "create table if not exists static_method_m1123 like static_method_m1;INSERT INTO static_method_m1 (id,name,age,create_time) values(2,'test',23,24367044)";
		// DruidPartion dr = new DruidPartion();
		// System.out.println(dr.doPartion(sql));

		String sqlx = "select distinct `value`,id, `type`, `key` from dictionaries where `type`='host_monitorfile' and `key`=?";
		String sql2 = "SELECT min(x) FROM db.TABLE1 as at1, TABLE2 ,TABLE3 as T3,TABLE4 t4,TABLE5, (SELECT * FROM TABLE6) LEFT OUTER JOIN TABLE7 as xyy "
				+ " WHERE ID = (SELECT avg(MAX(ID)),min(id),(select x from (select * from 2ddd)) FROM MY_TABLE8 ) AND ID2 IN (SELECT * FROM my_table9 JOIN TABLE11 as xx10) and y=? JOIN TABLE10 as xx10";
		String updatesql = "UPDATE dictionaries set x=123 where `type`='monitorfile'";
		String delsql = "delete from dictionaries set x=123 where `type`='monitorfile'";
		String insertSQL = "INSERT INTO dictionaries (x,y,z) values(1,2,3)";

		SQLStatementParser parser = new MySqlStatementParser(insertSQL);
		List<SQLStatement> stmtList = parser.parseStatementList();
		for (SQLStatement sqlStatement : stmtList) {
			MySqlInsertStatement st = (MySqlInsertStatement) sqlStatement;
			System.out.println(st.getTableName());
		}
//		long st = System.currentTimeMillis();
		// SQLStatementParser parser = new MySqlStatementParser(sqlx);
		// List<SQLStatement> stmtList = parser.parseStatementList();
		// SchemaStatVisitor c = new SchemaStatVisitor();
		// for (SQLStatement stmt : stmtList) {
		// stmt.accept(c);
		// System.out.println(c.getTables());
		// }
		// System.out.println(System.currentTimeMillis() - st);
//		sql2 = sql2.toLowerCase();
//		List<String> subSql = getSubQuery(sql2);
//		for (String string : subSql) {
//			sql2 = sql2.replace(string, "");
//		}
//		List<String> joninQury = getJoin(sql2);
//		for (String string : joninQury) {
//			sql2 = sql2.replace(string, "");
//		}
//		System.out.println(subSql);
//		System.out.println(joninQury);
//		System.out.println(sql2 + "--" + (System.currentTimeMillis() - st));
	}

	private static List<String> getJoin(String sql) {
		List<String> res = new LinkedList<String>();
		int start = sql.indexOf("left");
		if (start == -1) {
			start = sql.indexOf("outer");
		}
		if (start == -1) {
			start = sql.indexOf("join");
		}
		if (start == -1)
			return res;

		int keyLen = 6;// len of(where or union)
		int end = sql.indexOf("where");
		if (end == -1) {
			end = sql.indexOf("union");
		}
		if (end == -1) {
			end = sql.length();
		}
		String subStr = sql.substring(start, end);
		res.add(subStr);
		if (end != sql.length())
			res.addAll(getJoin(sql.substring(end + keyLen)));
		return res;
	}

	private static List<String> getSubQuery(String sql) {
		List<String> subSql = getSymmetricContent(sql, '(', ')');
		Iterator<String> it = subSql.iterator();
		while (it.hasNext()) {
			String tmp = it.next();
			if (!tmp.contains("select"))
				it.remove();
		}
		return subSql;
	}

	/***
	 * 获取字符窜里对称的内容
	 * 
	 * @param str
	 * @param open
	 * @param close
	 * @return
	 */
	private static List<String> getSymmetricContent(String str, char open, char close) {
		List<String> subStr = new LinkedList<String>();
		int len = str.length();
		for (int i = 0; i < len; i++) {
			char c = str.charAt(i);
			if (c != open)
				continue;
			int left = 1, right = 0, start = i;
			while (i++ < len) {
				c = str.charAt(i);
				if (c == open) {
					left++;
					int k = i, y = i;
					while (k < len && str.charAt(k++) != close)
						;
					subStr.add(str.substring(y, k + 1));
				} else if (c == close) {
					right++;
				}
				if (right == left)
					break;
			}
			subStr.add(str.substring(start, i + 1));
		}
		return subStr;
	}
}
